1 Imports System.Data.SqlClient
2 Imports System.IO
3
4 Public Class frmSchoolInfo
5 Private Sub auto()
6 Try
7 Dim Num As Integer = 0
8 con = New SqlConnection(cs)
9 con.Open()
10 Dim sql As String = ("SELECT MAX(S_ID) FROM SchoolInfo")
11 cmd = New SqlCommand(sql)
12 cmd.Connection = con
13 If (IsDBNull(cmd.ExecuteScalar)) Then
14 Num = 1
15 txtID.Text = Num.ToString
16 Else
17 Num = cmd.ExecuteScalar + 1
18 txtID.Text = Num.ToString
19 End If
20 cmd.Dispose()
21 con.Close()
22 con.Dispose()
23 Catch ex As Exception
24 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
25 End Try
26 End Sub
27 Sub fillCombo()
28 Try
29 con = New SqlConnection(cs)
30 con.Open()
31 adp = New SqlDataAdapter()
32 adp.SelectCommand = New SqlCommand("SELECT distinct RTRIM(Type) FROM SchoolType", con)
33 ds = New DataSet("ds")
34 adp.Fill(ds)
35 dtable = ds.Tables(0)
36 cmbSchoolType.Items.Clear()
37 For Each drow As DataRow In dtable.Rows
38 cmbSchoolType.Items.Add(drow(0).ToString())
39 Next
40 Catch ex As Exception
41 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
42 End Try
43 End Sub
44 Sub Reset()
45 txtAddress.Text = ""
46 txtAltContactNo.Text = ""
47 txtClass.Text = ""
48 txtContactNo.Text = ""
49 txtDiseNo.Text = ""
50 txtEmailID.Text = ""
51 txtEstablishedYear.Text = ""
52 txtFax.Text = ""
53 txtID.Text = ""
54 txtIndexNo.Text = ""
55 txtRegistrationNo.Text = ""
56 txtSchoolName.Text = ""
57 txtWebsite.Text = ""
58 cmbSchoolType.SelectedIndex = -1
59 PictureBox1.Image = My.Resources.kcK56zncj
60 txtSchoolName.Focus()
61 btnSave.Enabled = True
62 btnUpdate.Enabled = False
63 btnDelete.Enabled = False
64 auto()
65 End Sub
66 Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
67 Me.Close()
68 End Sub
69
70 Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
71 If txtSchoolName.Text = "" Then
72 MessageBox.Show("Please enter school name", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
73 txtSchoolName.Focus()
74 Return
75 End If
76 If txtAddress.Text = "" Then
77 MessageBox.Show("Please enter address", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
78 txtAddress.Focus()
79 Return
80 End If
81 If txtContactNo.Text = "" Then
82 MessageBox.Show("Please enter contact no.", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
83 txtContactNo.Focus()
84 Return
85 End If
86
87 If txtEmailID.Text = "" Then
88 MessageBox.Show("Please enter email id", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
89 txtEmailID.Focus()
90 Return
91 End If
92 If txtEstablishedYear.Text = "" Then
93 MessageBox.Show("Please enter established year", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
94 txtEstablishedYear.Focus()
95 Return
96 End If
97 If cmbSchoolType.Text = "" Then
98 MessageBox.Show("Please select school type", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
99 cmbSchoolType.Focus()
100 Return
101 End If
102 Try
103 con = New SqlConnection(cs)
104 con.Open()
105 Dim ct As String = "select SchoolName from SchoolInfo where SchoolName=@d1"
106 cmd = New SqlCommand(ct)
107 cmd.Parameters.AddWithValue("@d1", txtSchoolName.Text)
108 cmd.Connection = con
109 rdr = cmd.ExecuteReader()
110
111 If rdr.Read() Then
112 MessageBox.Show("School Name Already Exists", "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
113 txtSchoolName.Text = ""
114 txtSchoolName.Focus()
115 If (rdr IsNot Nothing) Then
116 rdr.Close()
117 End If
118 Return
119 End If
120 con = New SqlConnection(cs)
121 con.Open()
122 Dim cb As String = "insert into SchoolInfo( S_Id, SchoolName, Address, ContactNo, AltContactNo, FaxNo, Email, Website,RegistrationNo, DiseNo, IndexNo, EstablishedYear, Class, SchoolType,Logo) VALUES (@d1,@d2,@d3,@d4,@d5,@d6,@d7,@d8,@d9,@d10,@d11,@d12,@d13,@d14,@d15)"
123 cmd = New SqlCommand(cb)
124 cmd.Connection = con
125 cmd.Parameters.AddWithValue("@d1", txtID.Text)
126 cmd.Parameters.AddWithValue("@d2", txtSchoolName.Text)
127 cmd.Parameters.AddWithValue("@d3", txtAddress.Text)
128 cmd.Parameters.AddWithValue("@d4", txtContactNo.Text)
129 cmd.Parameters.AddWithValue("@d5", txtAltContactNo.Text)
130 cmd.Parameters.AddWithValue("@d6", txtFax.Text)
131 cmd.Parameters.AddWithValue("@d7", txtEmailID.Text)
132 cmd.Parameters.AddWithValue("@d8", txtWebsite.Text)
133 cmd.Parameters.AddWithValue("@d9", txtRegistrationNo.Text)
134 cmd.Parameters.AddWithValue("@d10", txtDiseNo.Text)
135 cmd.Parameters.AddWithValue("@d11", txtIndexNo.Text)
136 cmd.Parameters.AddWithValue("@d12", txtEstablishedYear.Text)
137 cmd.Parameters.AddWithValue("@d13", txtClass.Text)
138 cmd.Parameters.AddWithValue("@d14", cmbSchoolType.Text)
139 Dim ms As New MemoryStream()
140 Dim bmpImage As New Bitmap(PictureBox1.Image)
141 bmpImage.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg)
142 Dim data As Byte() = ms.GetBuffer()
143 Dim p As New SqlParameter("@d15", SqlDbType.Image)
144 p.Value = data
145 cmd.Parameters.Add(p)
146 cmd.ExecuteNonQuery()
147 con.Close()
148 Dim st As String = "added the School '" & txtSchoolName.Text & "' info"
149 LogFunc(lblUser.Text, st)
150 MessageBox.Show("Successfully saved", "School Info", MessageBoxButtons.OK, MessageBoxIcon.Information)
151 btnSave.Enabled = False
152 Getdata()
153 Catch ex As Exception
154 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
155 End Try
156 End Sub
157
158
159 Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
160 If txtSchoolName.Text = "" Then
161 MessageBox.Show("Please enter school name", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
162 txtSchoolName.Focus()
163 Return
164 End If
165 If txtAddress.Text = "" Then
166 MessageBox.Show("Please enter address", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
167 txtAddress.Focus()
168 Return
169 End If
170 If txtContactNo.Text = "" Then
171 MessageBox.Show("Please enter contact no.", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
172 txtContactNo.Focus()
173 Return
174 End If
175
176 If txtEmailID.Text = "" Then
177 MessageBox.Show("Please enter email id", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
178 txtEmailID.Focus()
179 Return
180 End If
181 If txtEstablishedYear.Text = "" Then
182 MessageBox.Show("Please enter established year", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
183 txtEstablishedYear.Focus()
184 Return
185 End If
186 If cmbSchoolType.Text = "" Then
187 MessageBox.Show("Please select school type", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
188 cmbSchoolType.Focus()
189 Return
190 End If
191 Try
192 con = New SqlConnection(cs)
193 con.Open()
194 Dim cb As String = "Update SchoolInfo set SchoolName=@d2, Address=@d3, ContactNo=@d4, AltContactNo=@d5, FaxNo=@d6, Email=@d7, Website=@d8,RegistrationNo=@d9, DiseNo=@d10, IndexNo=@d11, EstablishedYear=@d12, Class=@d13, SchoolType=@d14,Logo=@d15 where S_ID=@d1"
195 cmd = New SqlCommand(cb)
196 cmd.Connection = con
197 cmd.Parameters.AddWithValue("@d1", txtID.Text)
198 cmd.Parameters.AddWithValue("@d2", txtSchoolName.Text)
199 cmd.Parameters.AddWithValue("@d3", txtAddress.Text)
200 cmd.Parameters.AddWithValue("@d4", txtContactNo.Text)
201 cmd.Parameters.AddWithValue("@d5", txtAltContactNo.Text)
202 cmd.Parameters.AddWithValue("@d6", txtFax.Text)
203 cmd.Parameters.AddWithValue("@d7", txtEmailID.Text)
204 cmd.Parameters.AddWithValue("@d8", txtWebsite.Text)
205 cmd.Parameters.AddWithValue("@d9", txtRegistrationNo.Text)
206 cmd.Parameters.AddWithValue("@d10", txtDiseNo.Text)
207 cmd.Parameters.AddWithValue("@d11", txtIndexNo.Text)
208 cmd.Parameters.AddWithValue("@d12", txtEstablishedYear.Text)
209 cmd.Parameters.AddWithValue("@d13", txtClass.Text)
210 cmd.Parameters.AddWithValue("@d14", cmbSchoolType.Text)
211 Dim ms As New MemoryStream()
212 Dim bmpImage As New Bitmap(PictureBox1.Image)
213 bmpImage.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg)
214 Dim data As Byte() = ms.GetBuffer()
215 Dim p As New SqlParameter("@d15", SqlDbType.Image)
216 p.Value = data
217 cmd.Parameters.Add(p)
218 cmd.ExecuteNonQuery()
219 con.Close()
220 Dim st As String = "updated the School '" & txtSchoolName.Text & "' info"
221 LogFunc(lblUser.Text, st)
222 MessageBox.Show("Successfully updated", "School Info", MessageBoxButtons.OK, MessageBoxIcon.Information)
223 btnUpdate.Enabled = False
224 Getdata()
225 Catch ex As Exception
226 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
227 End Try
228 End Sub
229 Public Sub Getdata()
230 Try
231 con = New SqlConnection(cs)
232 con.Open()
233 cmd = New SqlCommand("SELECT RTRIM(S_Id), RTRIM(SchoolName), RTRIM(Address), RTRIM(ContactNo), RTRIM(AltContactNo), RTRIM(FaxNo), RTRIM(Email), RTRIM(Website),RTRIM(RegistrationNo), RTRIM(DiseNo), RTRIM(IndexNo), RTRIM(EstablishedYear), RTRIM(Class), RTRIM(SchoolType),Logo from SchoolInfo order by schoolname", con)
234 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
235 dgw.Rows.Clear()
236 While (rdr.Read() = True)
237 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6), rdr(7), rdr(8), rdr(9), rdr(10), rdr(11), rdr(12), rdr(13), rdr(14))
238 End While
239 con.Close()
240 Catch ex As Exception
241 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
242 End Try
243 End Sub
244 Private Sub btnNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNew.Click
245 Reset()
246 End Sub
247
248
249 Private Sub dgw_RowPostPaint(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles dgw.RowPostPaint
250 Dim strRowNumber As String = (e.RowIndex + 1).ToString()
251 Dim size As SizeF = e.Graphics.MeasureString(strRowNumber, Me.Font)
252 If dgw.RowHeadersWidth < Convert.ToInt32((size.Width + 20)) Then
253 dgw.RowHeadersWidth = Convert.ToInt32((size.Width + 20))
254 End If
255 Dim b As Brush = SystemBrushes.ControlText
256 e.Graphics.DrawString(strRowNumber, Me.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
257
258 End Sub
259
260 Private Sub frmRegistration_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
261 Getdata()
262 fillCombo()
263 End Sub
264
265 Private Sub dgw_MouseClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles dgw.MouseClick
266 Try
267 Dim dr As DataGridViewRow = dgw.SelectedRows(0)
268 txtID.Text = dr.Cells(0).Value.ToString()
269 txtSchoolName.Text = dr.Cells(1).Value.ToString()
270 txtAddress.Text = dr.Cells(2).Value.ToString()
271 txtContactNo.Text = dr.Cells(3).Value.ToString()
272 txtAltContactNo.Text = dr.Cells(4).Value.ToString()
273 txtFax.Text = dr.Cells(5).Value.ToString()
274 txtEmailID.Text = dr.Cells(6).Value.ToString()
275 txtWebsite.Text = dr.Cells(7).Value.ToString()
276 txtRegistrationNo.Text = dr.Cells(8).Value.ToString()
277 txtDiseNo.Text = dr.Cells(9).Value.ToString()
278 txtIndexNo.Text = dr.Cells(10).Value.ToString()
279 txtEstablishedYear.Text = dr.Cells(11).Value.ToString()
280 txtClass.Text = dr.Cells(12).Value.ToString()
281 cmbSchoolType.Text = dr.Cells(13).Value.ToString()
282 Dim data As Byte() = DirectCast(dr.Cells(14).Value, Byte())
283 Dim ms As New MemoryStream(data)
284 Me.PictureBox1.Image = Image.FromStream(ms)
285 btnUpdate.Enabled = True
286 btnSave.Enabled = False
287 btnDelete.Enabled = True
288 Catch ex As Exception
289 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
290 End Try
291 End Sub
292
293 Private Sub btnBrowse_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBrowse.Click
294 Try
295 With OpenFileDialog1
296 .Filter = ("Images |*.png; *.bmp; *.jpg;*.jpeg; *.gif;*.ico;")
297 .FilterIndex = 4
298 End With
299 'Clear the file name
300 OpenFileDialog1.FileName = ""
301 If OpenFileDialog1.ShowDialog() = DialogResult.OK Then
302 PictureBox1.Image = Image.FromFile(OpenFileDialog1.FileName)
303 End If
304 Catch ex As Exception
305 MsgBox(ex.ToString())
306 End Try
307 End Sub
308
309 Private Sub btnDelete_Click(sender As System.Object, e As System.EventArgs) Handles btnDelete.Click
310 Try
311 If MessageBox.Show("Do you really want to delete this record?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = Windows.Forms.DialogResult.Yes Then
312 DeleteRecord()
313 End If
314 Catch ex As Exception
315 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
316 End Try
317 End Sub
318 Private Sub DeleteRecord()
319
320 Try
321 Dim RowsAffected As Integer = 0
322 con = New SqlConnection(cs)
323 con.Open()
324 Dim cl As String = "select S_ID from SchoolInfo,Student where SchoolInfo.S_ID=Student.SchoolID and S_ID=@d1"
325 cmd = New SqlCommand(cl)
326 cmd.Connection = con
327 cmd.Parameters.AddWithValue("@d1", txtID.Text)
328 rdr = cmd.ExecuteReader()
329 If rdr.Read Then
330 MessageBox.Show("Unable to delete..Already in use in Student Entry", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
331 If Not rdr Is Nothing Then
332 rdr.Close()
333 End If
334 Exit Sub
335 End If
336 con = New SqlConnection(cs)
337 con.Open()
338 Dim cl1 As String = "select S_ID from SchoolInfo,Staff where SchoolInfo.S_ID=Staff.SchoolID and S_ID=@d1"
339 cmd = New SqlCommand(cl1)
340 cmd.Connection = con
341 cmd.Parameters.AddWithValue("@d1", txtID.Text)
342 rdr = cmd.ExecuteReader()
343 If rdr.Read Then
344 MessageBox.Show("Unable to delete..Already in use in Staff Entry", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
345 If Not rdr Is Nothing Then
346 rdr.Close()
347 End If
348 Exit Sub
349 End If
350 con = New SqlConnection(cs)
351 con.Open()
352 Dim cl2 As String = "select S_ID from SchoolInfo,Voucher where SchoolInfo.S_ID=Voucher.SchoolID and S_ID=@d1"
353 cmd = New SqlCommand(cl2)
354 cmd.Connection = con
355 cmd.Parameters.AddWithValue("@d1", txtID.Text)
356 rdr = cmd.ExecuteReader()
357 If rdr.Read Then
358 MessageBox.Show("Unable to delete..Already in use in Voucher Entry", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
359 If Not rdr Is Nothing Then
360 rdr.Close()
361 End If
362 Exit Sub
363 End If
364 con = New SqlConnection(cs)
365 con.Open()
366 Dim cl3 As String = "select S_ID from SchoolInfo,CourseFee where SchoolInfo.S_ID=CourseFee.SchoolID and S_ID=@d1"
367 cmd = New SqlCommand(cl3)
368 cmd.Connection = con
369 cmd.Parameters.AddWithValue("@d1", txtID.Text)
370 rdr = cmd.ExecuteReader()
371 If rdr.Read Then
372 MessageBox.Show("Unable to delete..Already in use in Class Fee Entry", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
373 If Not rdr Is Nothing Then
374 rdr.Close()
375 End If
376 Exit Sub
377 End If
378 con = New SqlConnection(cs)
379 con.Open()
380 Dim cl4 As String = "select S_ID from SchoolInfo,Installment_Hostel where SchoolInfo.S_ID=Installment_Hostel.SchoolID and S_ID=@d1"
381 cmd = New SqlCommand(cl4)
382 cmd.Connection = con
383 cmd.Parameters.AddWithValue("@d1", txtID.Text)
384 rdr = cmd.ExecuteReader()
385 If rdr.Read Then
386 MessageBox.Show("Unable to delete..Already in use in Hostel Installment Entry", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
387 If Not rdr Is Nothing Then
388 rdr.Close()
389 End If
390 Exit Sub
391 End If
392 con = New SqlConnection(cs)
393 con.Open()
394 Dim cq As String = "delete from SchoolInfo where S_id=" & txtID.Text & ""
395 cmd = New SqlCommand(cq)
396 cmd.Connection = con
397 RowsAffected = cmd.ExecuteNonQuery()
398 If RowsAffected > 0 Then
399 Dim st As String = "deleted the school '" & txtSchoolName.Text & "'"
400 LogFunc(lblUser.Text, st)
401 MessageBox.Show("Successfully deleted", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
402 Getdata()
403 Reset()
404 Else
405 MessageBox.Show("No Record found", "Sorry", MessageBoxButtons.OK, MessageBoxIcon.Information)
406 Reset()
407 End If
408 If con.State = ConnectionState.Open Then
409 con.Close()
410
411 End If
412 Catch ex As Exception
413 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
414 End Try
415 End Sub
416
417 Private Sub txtEstablishedYear_KeyPress(sender As System.Object, e As System.Windows.Forms.KeyPressEventArgs) Handles txtEstablishedYear.KeyPress
418 If (e.KeyChar < Chr(48) Or e.KeyChar > Chr(57)) And e.KeyChar <> Chr(8) Then
419 e.Handled = True
420 End If
421 End Sub
422 End Class